package com.vehiclemanagement.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.vehiclemanagement.entity.MaintenanceRecord;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 车辆维修保养记录Mapper接口
 */
@Mapper
public interface MaintenanceRecordMapper extends BaseMapper<MaintenanceRecord> {

    /**
     * 自定义分页查询，包含车辆信息
     *
     * @param page     分页对象
     * @param whereSql 自定义WHERE条件
     * @return 分页结果
     */
    @Select("SELECT m.*, v.plate_number, " +
            "CASE m.maintenance_type " +
            "   WHEN 1 THEN '定期保养' " +
            "   WHEN 2 THEN '故障维修' " +
            "   WHEN 3 THEN '事故维修' " +
            "   WHEN 4 THEN '保险理赔维修' " +
            "   WHEN 5 THEN '其他' " +
            "   ELSE '未知类型' " +
            "END AS maintenance_type_name, " +
            "CASE m.status " +
            "   WHEN 0 THEN '未开始' " +
            "   WHEN 1 THEN '进行中' " +
            "   WHEN 2 THEN '已完成' " +
            "   WHEN 3 THEN '已取消' " +
            "   ELSE '未知状态' " +
            "END AS status_name " +
            "FROM maintenance_record m " +
            "LEFT JOIN vehicle v ON m.vehicle_id = v.id " +
            "WHERE ${whereSql} " +
            "ORDER BY m.create_time DESC")
    IPage<MaintenanceRecord> selectPageWithInfo(Page<MaintenanceRecord> page, @Param("whereSql") String whereSql);

    /**
     * 获取指定车辆的维修保养总费用
     *
     * @param vehicleId 车辆ID
     * @return 维修保养总费用
     */
    @Select("SELECT COALESCE(SUM(total_cost), 0) FROM maintenance_record WHERE vehicle_id = #{vehicleId} AND status = 2 AND del_flag = 0")
    BigDecimal sumTotalCostByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 获取指定车辆的维修保养实际支出总费用（总费用-保险理赔）
     *
     * @param vehicleId 车辆ID
     * @return 实际支出总费用
     */
    @Select("SELECT COALESCE(SUM(actual_cost), 0) FROM maintenance_record WHERE vehicle_id = #{vehicleId} AND status = 2 AND del_flag = 0")
    BigDecimal sumActualCostByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 获取指定车辆的维修保养次数
     *
     * @param vehicleId 车辆ID
     * @return 维修保养次数
     */
    @Select("SELECT COUNT(*) FROM maintenance_record WHERE vehicle_id = #{vehicleId} AND status = 2 AND del_flag = 0")
    Integer countMaintenanceByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 按维修保养类型统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return 按类型统计的结果
     */
    @Select("SELECT maintenance_type, " +
            "COUNT(*) AS count, " +
            "SUM(total_cost) AS total_cost, " +
            "SUM(actual_cost) AS actual_cost " +
            "FROM maintenance_record " +
            "WHERE start_time BETWEEN #{startTime} AND #{endTime} " +
            "AND status = 2 AND del_flag = 0 " +
            "GROUP BY maintenance_type")
    List<Map<String, Object>> statisticsByType(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 按月份统计维修保养费用
     *
     * @param year 年份
     * @return 月度统计结果
     */
    @Select("SELECT DATE_FORMAT(start_time, '%Y-%m') AS month, " +
            "COUNT(*) AS count, " +
            "SUM(total_cost) AS total_cost, " +
            "SUM(actual_cost) AS actual_cost " +
            "FROM maintenance_record " +
            "WHERE YEAR(start_time) = #{year} " +
            "AND status = 2 AND del_flag = 0 " +
            "GROUP BY DATE_FORMAT(start_time, '%Y-%m') " +
            "ORDER BY month")
    List<Map<String, Object>> statisticsByMonth(@Param("year") Integer year);
    
    /**
     * 查询即将到期需要保养的车辆（基于里程数）
     *
     * @param mileageThreshold 里程数阈值（例如，距离上次保养已经行驶的里程数）
     * @return 需要保养的车辆列表
     */
    @Select("SELECT v.id, v.plate_number, v.current_mileage, MAX(m.maintenance_mileage) AS last_maintenance_mileage, " +
            "(v.current_mileage - MAX(m.maintenance_mileage)) AS mileage_diff " +
            "FROM vehicle v " +
            "LEFT JOIN maintenance_record m ON v.id = m.vehicle_id AND m.maintenance_type = 1 AND m.status = 2 AND m.del_flag = 0 " +
            "GROUP BY v.id, v.plate_number, v.current_mileage " +
            "HAVING (v.current_mileage - MAX(m.maintenance_mileage)) >= #{mileageThreshold} OR MAX(m.maintenance_mileage) IS NULL " +
            "ORDER BY mileage_diff DESC")
    List<Map<String, Object>> findVehiclesNeedingMaintenance(@Param("mileageThreshold") Double mileageThreshold);

    /**
     * 物理删除记录
     *
     * @param id 记录ID
     * @return 影响的行数
     */
    @Delete("DELETE FROM maintenance_record WHERE id = #{id}")
    int deletePhysically(@Param("id") Long id);
} 